/*
 * CustomerDAO.java
 *
 * Created on February 12, 2007, 9:10 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;
import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;


/**
 *
 * @author tkulbeth
 */
public class CustomerDAO {
    
    private static CustomerDAO instance = null;
    
    /** Creates a new instance of CustomerDAO */
    public CustomerDAO() {
    }
    
    /**Retrieves an instance of the CustomerDAO object**/
    public static synchronized CustomerDAO getInstance() {
        if (instance == null) {
            instance = new CustomerDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    public CustomerBO create() throws Exception{
        String id = GUID.generate();
        CustomerBO bo = new CustomerBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getCustID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized CustomerBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();

/*******************I commented this out due to the need to make sure current information is 
 *drawn from the central database and not the local cache.
 * 
//        CustomerBO member = (CustomerBO)c.get(id);
//        if (cust != null){
//            return cust;                                                        // if so, return it immediately
//        }
 **********************************************************************************************/
        CustomerBO cust = null;
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            cust = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return cust;
    }
    
    
    synchronized CustomerBO read(String id, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();

/*******************I commented this out due to the need to make sure current information is 
 *drawn from the central database and not the local cache.
 * 
//        CustomerBO member = (CustomerBO)c.get(id);
//        if (cust != null){
//            return cust;                                                        // if so, return it immediately
//        }
 **********************************************************************************************/
        CustomerBO cust = null;
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Customer WHERE CustomerID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            cust = new CustomerBO(rs.getString("customerid"));
            cust.setCustFirstName(rs.getString("custfirstname"));
            cust.setCustMI(rs.getString("custmi"));
            cust.setCustLastName(rs.getString("custlastname"));
            cust.setCustPhone(rs.getString("custphone"));
            cust.setCustEmail(rs.getString("custemail"));
            cust.setCustAddr(rs.getString("custaddr"));
            cust.setCustCity(rs.getString("custcity"));
            cust.setCustState(rs.getString("custstate"));
            cust.setCustZip(rs.getString("custzip"));
            if(rs.getString("MembershipID") == null){
                MembershipBO m = null;
                cust.setCustMembership(m);
            }else{
                MembershipBO m = MembershipDAO.getInstance().read(rs.getString("MembershipID"), conn);
                cust.setCustMembership(m);
            }
            cust.setAlreadyInDB(true);
            cust.setIsDirty(false);
            
            // save to the cache
            c.put(cust.getCustID(), cust);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return cust;
    }
    
    public synchronized CustomerBO readByMembershipID(String membId) throws SQLException, DataException, ConnectionPoolException {

        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Customer WHERE MembershipID = '" + membId + "'");
        conn.commit();
        
        CustomerBO cust = null;
        //build the object from the result set
        if (rs.next()) {
            cust = new CustomerBO(rs.getString("customerid"));
            cust.setCustFirstName(rs.getString("custfirstname"));
            cust.setCustMI(rs.getString("custmi"));
            cust.setCustLastName(rs.getString("custlastname"));
            cust.setCustPhone(rs.getString("custphone"));
            cust.setCustEmail(rs.getString("custemail"));
            cust.setCustAddr(rs.getString("custaddr"));
            cust.setCustCity(rs.getString("custcity"));
            cust.setCustState(rs.getString("custstate"));
            cust.setCustZip(rs.getString("custzip"));
            if(rs.getString("MembershipID") == null){
                MembershipBO m = null;
                cust.setCustMembership(m);
            }else{
                MembershipBO m = MembershipDAO.getInstance().read(rs.getString("MembershipID"), conn);
                cust.setCustMembership(m);
            }
            cust.setAlreadyInDB(true);
            cust.setIsDirty(false);
            
            // save to the cache
            Cache.getInstance().put(cust.getCustID(), cust);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return cust;
    }
    
    /*Search for a customer from UI input*/
    public synchronized ArrayList<CustomerBO> searchCust(String fname, String MI, String lname, String address,
            String city, String state, String zip, String phone, String email, boolean member) throws
            DataException, SQLException, ConnectionPoolException{
        
        ArrayList<CustomerBO> custList = new ArrayList<CustomerBO>();
        ArrayList<String> components = new ArrayList<String>();             //create a list to store query components
        
        boolean componentAbove = false;
        //determine what parameters are searchable
        if(fname != null){
            components.add(" custFirstName = '" + fname + "'");
            componentAbove = true; }                                              //indicates that a comma is needed to separate the following component
        if(MI != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custMI = '" + MI + "'");
            componentAbove = true;}
        if(lname != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custLastName = '" + lname + "'");
            componentAbove = true; }
        if(address != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custAddr = '" + address + "'");
            componentAbove = true; }
        if(city != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custCity = '" + city + "'");
            componentAbove = true;}
        if(state != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custState = '" + state + "'");
            componentAbove = true;}
        if(zip != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custZip = '" + zip + "'");
            componentAbove = true;}
        if(phone != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custPhone = '" + phone + "'");
            componentAbove = true;}
        if(email != null){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" custEmail = '" + email + "'");
            componentAbove = true;}
        if(member == true){
            if(componentAbove == true) components.add(" AND");                    //add a comma if needed
            components.add(" MembershipID IS NOT NULL");
            componentAbove = true;}
        if(member == false){
            if(componentAbove == true) components.add(" AND");
            components.add(" MembershipID IS NULL");
        }
        
        //now build the query
        String query = "SELECT * FROM CUSTOMER WHERE";
        for(int i=0; i<components.size(); i++){
            query = query.concat(components.get(i));
        }
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            MembershipBO m = null;
            if(rs.getString("MembershipID") != null){
                m = MembershipDAO.getInstance().read(rs.getString("MembershipID"), conn);
            }
            
            CustomerBO newCust = new CustomerBO(rs.getString("customerID"), rs.getString("custFirstName"), rs.getString("custMI"),
                    rs.getString("custLastName"), rs.getString("custAddr"), rs.getString("custCity"),
                    rs.getString("custState"), rs.getString("custZip"), rs.getString("custPhone"),
                    rs.getString("custEmail"), m);
            newCust.setAlreadyInDB(true);
            newCust.setIsDirty(false);
            custList.add(newCust);
        }
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return custList;
    }
    
    /*Retrieve list of all customers*/
    public synchronized ArrayList<CustomerBO> readAllCust() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<CustomerBO> custList = new ArrayList<CustomerBO>();
        //now build the query
        String query = "SELECT * FROM CUSTOMER";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            MembershipBO m = null;
            if(rs.getString("MembershipID") != null){
                m = MembershipDAO.getInstance().read(rs.getString("MembershipID"), conn);
            }
            
            CustomerBO newCust = new CustomerBO(rs.getString("customerID"), rs.getString("custFirstName"), rs.getString("custMI"),
                    rs.getString("custLastName"), rs.getString("custAddr"), rs.getString("custCity"),
                    rs.getString("custState"), rs.getString("custZip"), rs.getString("custPhone"),
                    rs.getString("custEmail"), m);
            newCust.setAlreadyInDB(true);
            newCust.setIsDirty(false);
            custList.add(newCust);
        }
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return custList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(CustomerBO custIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(custIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + custIn.getCustID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        return;
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(CustomerBO cust, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // call save(bo, conn) on any subobjects (like CustomerDAO to MembershipDAO)
        if(cust.getCustMembership() != null && cust.getCustMembership().isIsDirty() == true){
            MembershipDAO.getInstance().save(cust.getCustMembership(), conn);
        }
        
        // run update or insert
        if (cust.isIsDirty()) {
            if (cust.isAlreadyInDB()) {
                update(cust, conn);
            }else{
                insert(cust, conn);
            }
            // set the dirty flag to false now that we've saved it
            cust.setIsDirty(false);
        }
        
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(cust.getCustID(), cust);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(CustomerBO cust, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Customer SET CustFirstName = ?, CustMI = ?, CustLastName = ?," +
                "custAddr = ?, custCity = ?, custState = ?, custZip = ?, custPhone = ?, custEmail = ? WHERE CustomerID = ?");
        pstmt.setString(1, cust.getCustFirstName());
        pstmt.setString(2, cust.getCustMI());
        pstmt.setString(3, cust.getCustLastName());
        pstmt.setString(4, cust.getCustAddr());
        pstmt.setString(5, cust.getCustCity());
        pstmt.setString(6, cust.getCustState());
        pstmt.setString(7, cust.getCustZip());
        pstmt.setString(8, cust.getCustPhone());
        pstmt.setString(9, cust.getCustEmail());
        pstmt.setString(10, cust.getCustID());
        pstmt.executeUpdate();
        
        pstmt = conn.prepareStatement("UPDATE Customer SET MembershipID = ? WHERE CustomerID = ?");
        //if the customer is a new member, update the respective field
        if(cust.getCustMembership() != null){
            pstmt.setString(1, cust.getCustMembership().getMembershipID());
            pstmt.setString(2, cust.getCustID());
            pstmt.executeUpdate();
        }else{
            pstmt.setNull(1, Types.VARCHAR);
            pstmt.setString(2, cust.getCustID());
            pstmt.executeUpdate();
        }
        
        pstmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(CustomerBO cust, Connection conn) throws SQLException, DataException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Customer (CustomerID, custFirstName, custMI, " +
                "custLastName, custAddr, custCity, custState, custZip, custPhone, custEmail) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, cust.getCustID());
        pstmt.setString(2, cust.getCustFirstName());
        pstmt.setString(3, cust.getCustMI());
        pstmt.setString(4, cust.getCustLastName());
        pstmt.setString(5, cust.getCustAddr());
        pstmt.setString(6, cust.getCustCity());
        pstmt.setString(7, cust.getCustState());
        pstmt.setString(8, cust.getCustZip());
        pstmt.setString(9, cust.getCustPhone());
        pstmt.setString(10, cust.getCustEmail());
        pstmt.executeUpdate();
        
        pstmt = conn.prepareStatement("UPDATE Customer SET MembershipID = ? WHERE CustomerID = ?");
        //if the customer is a new member, update the respective field
        if(cust.getCustMembership() != null){
            pstmt.setString(1, cust.getCustMembership().getMembershipID());
            pstmt.setString(2, cust.getCustID());
            pstmt.executeUpdate();
        }else{
            pstmt.setNull(1, Types.VARCHAR);
            pstmt.setString(2, cust.getCustID());
            pstmt.executeUpdate();
        }
        // tell the object that it's now in the db (so we call update next time not insert)
        cust.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    //I did not include the DELETE functionality intentionally due to instructions received, as well as
    //to protect the integrity of the data.
    
}
